package org.jackysoft.util;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

public final class ExcelUtil {
    static final Log logger = LogFactory.getLog(ExcelUtil.class);
	public static Double cellNumber(Cell cell) {
		if(cell==null)return 0D;
		Double value = 0D;
		try {
			value = cell.getNumericCellValue();
		} catch (Exception e) {
			logger.error("cell type "+cell.getCellType()+" is not number cell, real value is "+cellString(cell));
			return 0D;
		}
		return value;

	}
	
	public static Cell nextCell(Iterator<Cell> cells) {
		if (cells == null)
			return null;
		return cells.hasNext() ? cells.next() : null;
	}
	
	public static String nextCellString(Iterator<Cell> cells){
		return cellString(nextCell(cells));
	}

	public static Double nextCellDouble(Iterator<Cell> cells){
		String val = nextCellString(cells);
	  	
		try{
			double d = Double.parseDouble(val);
		    return d;
		}catch(NumberFormatException ne){
			return 0D;
		}
	}
	
	/**
	 * @param c
	 *            Excel cell 对象
	 * @return 过滤空白的字符窜
	 * */
	public static String cellString(Cell c) {
		return cellString(c,false);
	}
	/**
	 * 
	 * */
	public static String cellString(Cell c ,boolean formatedNumber){
		String cv = "";
		if (c == null)
			return cv;
		switch (c.getCellType()) {
		case Cell.CELL_TYPE_BLANK:
		case Cell.CELL_TYPE_STRING:
			cv = StringUtils.trimToEmpty(c.getStringCellValue());
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			cv = StringUtils.trimToEmpty(c.getBooleanCellValue() + "");
			break;
		case Cell.CELL_TYPE_FORMULA:
		case Cell.CELL_TYPE_NUMERIC:
			String dpattern =formatedNumber?"######0.0000":"######0";
			DecimalFormat df = new DecimalFormat(dpattern);
			cv =df.format(c.getNumericCellValue());		   
			break;
		case Cell.CELL_TYPE_ERROR:
			cv = StringUtils.trimToEmpty(c.getErrorCellValue() + "");
			break;
		
		}
		return cv;
	}

	public static void copyAndCreateCell(Cell from, Row row) {
		if (from == null || row == null)
			return;
		Cell ncell = row.createCell(from.getColumnIndex(), from.getCellType());
		switch (ncell.getCellType()) {
		case Cell.CELL_TYPE_BLANK:
		case Cell.CELL_TYPE_STRING:
			String cv = StringUtils.trimToEmpty(from.getStringCellValue());
			ncell.setCellValue(cv);
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			boolean  bul  = from.getBooleanCellValue();
			ncell.setCellValue(bul);
			break;
		case Cell.CELL_TYPE_NUMERIC:
			ncell.setCellValue(from.getNumericCellValue());
			break;
		case Cell.CELL_TYPE_ERROR:
			ncell.setCellErrorValue(from.getErrorCellValue());
			break;
		case Cell.CELL_TYPE_FORMULA:
			ncell.setCellFormula(from.getCellFormula());
			break;
		}
	}
	public static String rowString(Row row){
		Iterator<Cell> itr = row.cellIterator();
		String str = "";
		while(itr.hasNext()){
			str+=cellString(itr.next())+",";
		}
		return str;
	}
	
	
	
	
	/**
	 * 从code name形式的cell里分离出code字符串
	 * 
	 * @param cell
	 *            codename所在的cell实例
	 * @return code 值
	 * */
	public static String parseCode(Cell cell) {
		String codeName = cellString(cell);
		String[] cns = codeName.split("\\s+");
		return cns == null ? null : (cns[0]);
	}
	
	
	
	/**从一个输入字节数组里解析计划内容*/
	public static  void parseForImport(byte[] content, RowWatcher rw) {
		if (content == null || content.length == 0)
			return;
		ByteArrayInputStream bis = new ByteArrayInputStream(content);
		try {
			HSSFWorkbook wb = new HSSFWorkbook(bis);
			int sheetCount = wb.getNumberOfSheets();
			for (int s = 0; s < sheetCount; s++) {
				Sheet sheet = wb.getSheetAt(s);
				rw.setSheetKey(StringUtils.trim(sheet.getSheetName()));
				Iterator<Row> rowItr = sheet.rowIterator();
				while (rowItr.hasNext()) {
					rw.accept(rowItr.next());
				}
			}

		} catch (IOException e) {
			logger.error(e.toString());
			e.printStackTrace();
		}
	}
	
	
	/**
	 * @
	 * 
	 */
	public static byte[] mergeTwoExcel(Collection<InputStream> ilist) {
		ByteArrayOutputStream sout = new ByteArrayOutputStream();
		Set<String> names = new HashSet<String>();
		ArrayList<Sheet> talSheets = new ArrayList<Sheet>(ilist.size());
		
		return sout.toByteArray();
	}

	public static byte[] mergeTwoExcelPOI(Collection<InputStream> list) {
		byte[] bs = new byte[0];
		return bs;
	}
}
